/*
	Returns list of Programming_Release rows based on the given search critera
	Usage: exec sp_search_program_release '-1', '-1', '-1', '-1', null, '-1', 5
*/

ALTER PROC sp_search_program_release(
@process_step_id VARCHAR(10),
@location_id VARCHAR(10),
@customer_id VARCHAR(10),
@status VARCHAR(50),
@salesOrder_No VARCHAR(50),
@year VARCHAR(10), 
@vendor_Id VARCHAR(10))

AS
BEGIN

	DECLARE @SQLString NVARCHAR(2000)
	DECLARE @ParamDefinition NVARCHAR(500)

	SET @SQLString = N'SELECT SO_IS_Sample, PR.Prog_Release_Id, PR.Prog_Release_No, SO.SalesOrder_No, SO.SO_Customer_PO_Number, COOL.COOL_Line_Item_No, P.Part_Number, P.P_ProgramCode, C.C_Name, PR.OrderQty, CONVERT( VARCHAR, PR.Issue_date, 101) Issue_date, V.V_Name, PR.Status, COOL.COOL_Shipment_No, CONVERT( VARCHAR, PR.Expected_Completion_Date, 101) Expected_Completion_Date, P.P_Comments FROM Program_Release PR, Sales_Order SO, Customer_open_Order_LineItem COOL, Product P, Customer C, Vendor V WHERE PR.SalesOrder_id = SO.SalesOrder_id AND PR.LineItem_id = COOL.LineItem_id AND COOL.Product_id = P.Product_Id AND SO.Customer_ID = C.Customer_ID AND PR.Vendor_Id = V.Vendor_ID '
	SET @ParamDefinition = N'@Prm_ProcessStep_ID VARCHAR(10), @Prm_Location_ID VARCHAR(10), @Prm_Customer_ID VARCHAR(10), @Prm_Status VARCHAR(50), @Prm_Sales_Order_No varchar(50), @Prm_Release_Year varchar(10), @Prm_Vendor_Id varchar(10)'

	IF @process_step_id <> '-1'
	BEGIN
		SET @SQLString = @SQLString + ' AND P.ProcessStep_id = @Prm_ProcessStep_ID'
	END

	IF @customer_id <> '-1' AND @customer_id <> '0' 
	BEGIN
		SET @SQLString = @SQLString + ' AND C.Customer_id = @Prm_Customer_ID'
	END

	IF @location_id <> '-1' AND @location_id <> '0' 
	BEGIN
		SET @SQLString = @SQLString + ' AND V.Vendor_id = @Prm_Location_ID'
	END

	IF @status <> '-1'
	BEGIN
		SET @SQLString = @SQLString + ' AND PR.Status = @Prm_Status'
	END

	IF @salesOrder_No IS NOT NULL AND LEN( RTRIM( LTRIM( @salesOrder_No ) ) ) > 0
	BEGIN
		SET @SQLString = @SQLString + ' AND SO.SalesOrder_No = @Prm_Sales_Order_No'
	END

	IF @year <> '-1'
	BEGIN
		SET @SQLString = @SQLString + ' AND DATEPART( YEAR, PR.Issue_date ) = @Prm_Release_Year'
	END

	IF( @vendor_Id <> '-1' )
	BEGIN
		SET @SQLString = @SQLString + ' AND PR.Vendor_Id = @Prm_Vendor_Id'
	END
	
	SET @SQLString = @SQLString + ' ORDER BY CONVERT(DATETIME, Issue_date, 101) DESC, PR.Prog_Release_Id DESC '

	EXECUTE SP_EXECUTESQL @SQLString, @ParamDefinition, @Prm_ProcessStep_ID = @process_step_id, @Prm_Customer_ID = @customer_id, @Prm_Location_ID = @location_id, 
	@Prm_Status = @status, @Prm_Sales_Order_No = @salesOrder_No, @Prm_Release_Year = @year, @Prm_Vendor_Id = @vendor_id 


END
